 <?php
	/**
 * Enter description here ...
 * @author josue
 *
 * tags
 */
 class Dbclient_model extends CI_Model{
 	
 	/* Para cada método, tenemos que abrir una conexión a la base de datos del cliente:
	 * s
	 * 
	 * y cerrarla de esta forma:  $this->db->close();*/

 	public function __construct(){
 		parent::__construct();
 		//cargando la base de datos personalizada del usuario
 		$config['hostname'] = 'megan.teksar.com.mx';
		$config['username'] = $this->session->userdata('username');
		$config['password'] = $this->session->userdata('mysqlPass');
		$config['database'] = $this->session->userdata('dbname');
		$config['dbdriver'] = 'mysql';
		$config['dbprefix'] = '';
		$config['pconnect'] = TRUE;
		$config['db_debug'] = TRUE;
		$config['cache_on'] = FALSE;
		$config['cachedir'] = '';
		$config['char_set'] = 'utf8';
		$config['dbcollat'] = 'utf8_general_ci';
		$config['autoinit'] = TRUE;
		$config['swap_pre'] = '';
		$config['stricton'] = FALSE;
 			
 		$this->load->database($config);
 	}
 	
 	public function get_data($tipo_reporte,$variable,$date,$time,$numDias){
 		$resultSet = null;
 		switch ($tipo_reporte) {
 			case REPORTE_MULTIPLE:
 				$resultSet = $this->get_reporte_intervalo($date,$variable,$numDias);
 			break;
 			case REPORTE_POR_DIA:
 				$resultSet = $this->get_reporte_dia($date,$variable);
 			break;
 			case REPORTE_POR_HORA:
 				$resultSet = $this->get_reporte_hora($date,$time,$variable);
 			break;
 			default:
 				
 			break;
 		}
 		return $resultSet;
 	}
 	
 	
 	private function get_reporte_intervalo($date,$variable,$numDias){
 		$dateArray = preg_split("/\./", $date);
 		$dia = $dateArray[0];
 		$mes = $dateArray[1];
 		$anio = $dateArray[2];
 			
 		
 		//discriminando query por tipo de variable (Voltaje y Corriente  u otro)
 		if(preg_match_all("/^Voltage_*|^Current_*/",$variable, $matches) != 0){
 			$query = "Select Date_Format(tkm_log.fecha, '%d-%m-%y') As Fecha,
					  ROUND(Avg(tkm_log." . $variable . "A), 2) As Promedio_A, Max(tkm_log." . $variable ."A) As
					  Maximo_A, Min(tkm_log." . $variable . "A) As Minimo_A,
					  ROUND(Avg(tkm_log." . $variable . "B), 2) As Promedio_B, Max(tkm_log." . $variable ."B) As
					  Maximo_B, Min(tkm_log.". $variable ."B) As Minimo_B,
					  ROUND(Avg(tkm_log." . $variable . "C), 2) As Promedio_C, Max(tkm_log." . $variable ."C) As
					  Maximo_C, Min(tkm_log." . $variable . "C) As Minimo_C
					From tkm_log,
					  (Select Date_Format(Concat(tkm_year.tkyears, '-', tkm_month.tkmonths, '-',
					      tkm_day.tkdays), '%y-%m-%d') As fechaparametro
					    From tkm_month,
					      tkm_year,
					      tkm_day
					    Where tkm_year.tkyears = " . $anio . " And tkm_month.tkmonths = " . $mes . " And
					      tkm_day.tkdays = " . $dia . ") GetDateParameter,
					  (Select tkm_intervalo.tknumdays As numDias
					    From tkm_intervalo
					    Where tkm_intervalo.tknumdays = " . $numDias .") getIntervalo
					Where tkm_log.fecha Between Cast(GetDateParameter.fechaparametro As DateTime)
					  And Date_Add(Cast(GetDateParameter.fechaparametro As DateTime), Interval
					  getIntervalo.numDias Day) And tkm_log." . $variable . "A != -1
					Group By Date(tkm_log.fecha)
					Order By tkm_log.fecha";
 		}else{
 			
 			$query = "Select Date_Format(tkm_log.fecha, '%d-%m-%y') As Fecha,
					  ROUND(Avg(tkm_log." . $variable . "), 2) As Promedio, Max(tkm_log." . $variable . ")
					  As Maxima, Min(tkm_log." . $variable . ") As Minima
					From tkm_log,
					  (Select Date_Format(Concat(tkm_year.tkyears, '-', tkm_month.tkmonths, '-',
					      tkm_day.tkdays), '%y-%m-%d') As fechaparametro
					    From tkm_month,
					      tkm_year,
					      tkm_day
					    Where tkm_year.tkyears = " . $anio . " And tkm_month.tkmonths = " . $mes . " And
					      tkm_day.tkdays = " . $dia . ") GetDateParameter,
					  (Select tkm_intervalo.tknumdays As numDias
					    From tkm_intervalo
					    Where tkm_intervalo.tknumdays = " . $numDias .") getIntervalo
					Where tkm_log.fecha Between Cast(GetDateParameter.fechaparametro As DateTime)
					  And Date_Add(Cast(GetDateParameter.fechaparametro As DateTime), Interval
					  getIntervalo.numDias Day)
					Group By Date(tkm_log.fecha)
					Order By tkm_log.fecha";
 		}
 		$resultSet = $this->db->query($query);
 		$this->db->close();
 		return $resultSet->result();
 	}
 	
 	private function get_reporte_dia($date,$variable){
 		$dateArray = preg_split("/\./", $date);
 		$dia = $dateArray[0];
 		$mes = $dateArray[1];
 		$anio = $dateArray[2];
 		
 		//discriminando query por tipo de variable (Voltaje y Corriente  u otro)
 		if(preg_match_all("/^Voltage_*|^Current_*/",$variable, $matches) != 0){
 			//solo se presenta por voltaje, no por fase, eliminar las fases en el select box, solo poner voltajes
 			$query = "Select Date_Format(tkm_log.fecha, '%H:%i') As Fecha,
					  ROUND(Avg(tkm_log.".$variable."A), 2) As Promedio_A, Max(tkm_log.".$variable."A) As
					  Maximo_A, Min(tkm_log.".$variable."A) As Minimo_A ,
					  ROUND(Avg(tkm_log.".$variable."B), 2) As Promedio_B, Max(tkm_log.".$variable."B) As
					  Maximo_B, Min(tkm_log.".$variable."B) As Minimo_B,
					  ROUND(Avg(tkm_log.".$variable."C), 2) As Promedio_C, Max(tkm_log.".$variable."C) As
					  Maximo_C, Min(tkm_log.".$variable."C) As Minimo_C
					From tkm_log,
					  (Select Date_Format(Concat(tkm_year.tkyears, '-', tkm_month.tkmonths, '-',
					      tkm_day.tkdays), '%y-%m-%d') As fechaparametro
					    From tkm_month,
					      tkm_year,
					      tkm_day
					    Where tkm_year.tkyears = ".$anio." And tkm_month.tkmonths = ".$mes." And
					      tkm_day.tkdays = ".$dia.") GetDateParameter
					Where tkm_log.fecha Between Cast(GetDateParameter.fechaparametro As DateTime)
					  And Date_Add(Cast(GetDateParameter.fechaparametro As DateTime), Interval 24
					  Hour) And tkm_log.".$variable."A != -1
					Group By ((60 / 4) * Hour(tkm_log.fecha) + FLOOR(Minute(tkm_log.fecha) / 15))
					Order By tkm_log.fecha";
 			
 		}else{
 			$query = "Select Date_Format(tkm_log.fecha, '%H:%i') As Fecha,
 			 			ROUND(Avg(tkm_log." . $variable . "), 2) As Promedio, Max(tkm_log." . $variable . ") As
 			 			Maxima, Min(tkm_log." . $variable . ") As Minima
 			 			From tkm_log,
 			 			  (Select Date_Format(Concat(tkm_year.tkyears, '-', tkm_month.tkmonths, '-',
 			 			      tkm_day.tkdays), '%y-%m-%d') As fechaparametro
 			 				    From tkm_month,
 			 				     tkm_year,
 			 				     tkm_day
 			 				    Where tkm_year.tkyears = " . $anio . " And tkm_month.tkmonths = " . $mes . " And
 			 				     tkm_day.tkdays = " . $dia . ") GetDateParameter
 			 			Where tkm_log.fecha Between Cast(GetDateParameter.fechaparametro As DateTime)
 			 				And Date_Add(Cast(GetDateParameter.fechaparametro As DateTime), Interval 24
 			 				Hour) And tkm_log." . $variable . " != -1
 			 			Group By ((60 / 4) * Hour(tkm_log.fecha) + FLOOR(Minute(tkm_log.fecha) / 15))
 			 			Order By tkm_log.fecha;";
 			
 		}
 		 $resultSet = $this->db->query($query);
 		 $this->db->close();
 		 
		return $resultSet->result();
 	}
 	
 	private function get_reporte_hora($date,$time,$variable){
 		$dateArray = preg_split("/\./", $date);
 		$dia = $dateArray[0];
 		$mes = $dateArray[1];
 		$anio = $dateArray[2];
 		
 		$timeArray = preg_split("/:/", $time);
 		$hora = $timeArray[0];
 		$minuto = $timeArray[1];
 		//discriminando query por tipo de variable (Voltaje y Corriente  u otro)
 		if(preg_match_all("/^Voltage_*|^Current_*/",$variable, $matches) != 0){
 			
 			$query = "Select Date_Format(tkm_log.fecha, '%H:%i') As Fecha,
					  ROUND(Avg(tkm_log.". $variable . "A), 2) As Promedio_A, Max(tkm_log.".$variable."A) As
					  Maximo_A, Min(tkm_log." . $variable ."A) As Minimo_A,
					  ROUND(Avg(tkm_log." . $variable . "B), 2) As Promedio_B, Max(tkm_log.". $variable ."B) As
					  Maximo_B, Min(tkm_log.".$variable."B) As Minimo_B,
					  ROUND(Avg(tkm_log.".$variable."C), 2) As Promedio_C, Max(tkm_log.".$variable."C) As
					  Maximo_C, Min(tkm_log.".$variable."C) As Minimo_C
					From tkm_log,
					  (Select Distinct Date_Format(Concat(tkm_year.tkyears, '-', tkm_month.tkmonths,
					      '-', tkm_day.tkdays, ' ', tkm_hours.tkhoursofday, ':',
					      tkm_minutes.tkminute), '%y-%m-%d %H:%i') As fechaparametro
					    From tkm_month,
					      tkm_year,
					      tkm_day,
					      tkm_hours,
					      tkm_minutes
					    Where tkm_year.tkyears = " . $anio . " And tkm_month.tkmonths = " . $mes . " And
					      tkm_day.tkdays = " . $dia . " And tkm_hours.tkhoursofday = ".$hora." And
					      tkm_minutes.tkminute = " . $minuto . ") GetDateParameter
					Where tkm_log.fecha Between Cast(GetDateParameter.fechaparametro As DateTime)
					  And Date_Add(Cast(GetDateParameter.fechaparametro As DateTime), Interval 1
					  Hour) And tkm_log." . $variable . "A != -1
					Group By Minute(tkm_log.fecha)
					Order By tkm_log.fecha";
					 			
 		}else{
 			
 			$query = "Select Date_Format(tkm_log.fecha, '%H:%i') As Fecha,
					  ROUND(Avg(tkm_log.". $variable . "), 2) As Promedio, Max(tkm_log." . $variable . ") As
					  Maxima, Min(tkm_log." . $variable .") As Minima
					From tkm_log,
					  (Select Distinct Date_Format(Concat(tkm_year.tkyears, '-', tkm_month.tkmonths,
					      '-', tkm_day.tkdays, ' ', tkm_hours.tkhoursofday, ':',
					      tkm_minutes.tkminute), '%y-%m-%d %H:%i') As fechaparametro
					    From tkm_month,
					      tkm_year,
					      tkm_day,
					      tkm_hours,
					      tkm_minutes
					    Where tkm_year.tkyears = " . $anio . " And tkm_month.tkmonths = " . $mes . " And
					      tkm_day.tkdays = " . $dia . " And tkm_hours.tkhoursofday = " . $hora . " And
					      tkm_minutes.tkminute = " . $minuto . ") GetDateParameter
					Where tkm_log.fecha Between Cast(GetDateParameter.fechaparametro As DateTime)
					  And Date_Add(Cast(GetDateParameter.fechaparametro As DateTime), Interval 1
					  Hour) And tkm_log." . $variable . " != -1
					Group By Minute(tkm_log.fecha)
					Order By Hour(tkm_log.fecha), Minute(tkm_log.fecha)
					 			";
 		}
 		
 		$resultSet = $this->db->query($query);
 		$this->db->close();
 		
 		return $resultSet->result();
 			
 	}
 	
// 	public function __destruct(){
// 		parent::__destruct();
// 		$this->db->close();
// 		
// 	}
 	
 }
	
?>